using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace SouthernLand.Common.DAL.Advertise
{
    public class AdvertiseDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new Advertise instance filled with the DataReader's current record data 
        /// </summary>
        private BOL.Advertise.Advertise GetAdvertiseFromReader(IDataReader dr)
        {
            var obj = new BOL.Advertise.Advertise();
            obj.AdvID = (dr["AdvID"] is DBNull) ? Int32.MinValue : (Int32) dr["AdvID"];
            obj.FileName = (dr["FileName"] is DBNull) ? String.Empty : (String) dr["FileName"];
            obj.AdvWidth = (dr["AdvWidth"] is DBNull) ? 0 : (Int32) dr["AdvWidth"];
            obj.AdvHeight = (dr["AdvHeight"] is DBNull) ? 0 : (Int32) dr["AdvHeight"];
            obj.AdvURL = (dr["AdvURL"] is DBNull) ? String.Empty : (String) dr["AdvURL"];
            obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime) dr["AddedDate"];
            obj.RegionDate = (dr["RegionDate"] is DBNull) ? DateTime.MaxValue : (DateTime) dr["RegionDate"];
            obj.ExpireDate = (dr["ExpireDate"] is DBNull) ? DateTime.MaxValue : (DateTime) dr["ExpireDate"];
            obj.ClickCount = (dr["ClickCount"] is DBNull) ? Int32.MinValue : (Int32) dr["ClickCount"];
            obj.CateID = (dr["CateID"] is DBNull) ? -1 : (Int32) dr["CateID"];
            obj.AdvPosition = (dr["AdvPosition"] is DBNull) ? Int32.MinValue : (Int32) dr["AdvPosition"];
            obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue : (Int32) dr["DisplayOrder"];
            obj.IsActive = (dr["IsActive"] is DBNull) ? false : (Boolean) dr["IsActive"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of Advertise with the data read from the input DataReader
        /// </summary>
        private List<BOL.Advertise.Advertise> GetAdvertiseCollectionFromReader(IDataReader dr)
        {
            var obj = new List<BOL.Advertise.Advertise>();
            while (dr.Read())
                obj.Add(GetAdvertiseFromReader(dr));
            return obj;
        }

        public int Insert(BOL.Advertise.Advertise obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Advertise_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@AdvID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myCommand.Parameters.Add("@FileName", SqlDbType.VarChar).Value = obj.FileName;
                myCommand.Parameters.Add("@AdvWidth", SqlDbType.Int).Value = obj.AdvWidth;
                myCommand.Parameters.Add("@AdvHeight", SqlDbType.Int).Value = obj.AdvHeight;
                myCommand.Parameters.Add("@AdvURL", SqlDbType.VarChar).Value = obj.AdvURL;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@RegionDate", SqlDbType.DateTime).Value = obj.RegionDate;
                myCommand.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = obj.ExpireDate;
                myCommand.Parameters.Add("@ClickCount", SqlDbType.Int).Value = obj.ClickCount;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@AdvPosition", SqlDbType.Int).Value = obj.AdvPosition;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                int ret = myCommand.ExecuteNonQuery();
                return (int) myCommand.Parameters["@AdvID"].Value;
            }
        }

        public bool Update(BOL.Advertise.Advertise obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Advertise_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@AdvID", SqlDbType.Int).Value = obj.AdvID;
                myCommand.Parameters.Add("@FileName", SqlDbType.VarChar).Value = obj.FileName;
                myCommand.Parameters.Add("@AdvWidth", SqlDbType.Int).Value = obj.AdvWidth;
                myCommand.Parameters.Add("@AdvHeight", SqlDbType.Int).Value = obj.AdvHeight;
                myCommand.Parameters.Add("@AdvURL", SqlDbType.VarChar).Value = obj.AdvURL;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@RegionDate", SqlDbType.DateTime).Value = obj.RegionDate;
                myCommand.Parameters.Add("@ExpireDate", SqlDbType.DateTime).Value = obj.ExpireDate;
                myCommand.Parameters.Add("@ClickCount", SqlDbType.Int).Value = obj.ClickCount;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@AdvPosition", SqlDbType.Int).Value = obj.AdvPosition;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 AdvID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Advertise_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@AdvID", SqlDbType.Int).Value = AdvID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the w4u_Advertise with the specified ID
        /// </summary>
        public BOL.Advertise.Advertise GetAdvertiseByID(Int32 AdvID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Advertise_GetAdvertiseByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@AdvID", SqlDbType.Int).Value = AdvID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetAdvertiseFromReader(dr) : null);
            }
        }

        public List<BOL.Advertise.Advertise> GetAdvertiseByPosition(int AdvPosition)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Advertise_GetAdvertiseByPosition", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@AdvPosition", SqlDbType.Int).Value = AdvPosition;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetAdvertiseCollectionFromReader(dr);
            }
        }

        //Get All Advertise
        public List<BOL.Advertise.Advertise> GetAllAdvertise(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Advertise_GetAllAdvertise", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetAdvertiseCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  Advertise
        public int GetCountAdvertise()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Advertise_GetCountAdvertise", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }
    }
}